CREATE  PROCEDURE [dbo].[asi_SalesTeamChangeRange]
    @salesTeamKey uniqueidentifier,
    @oldStartDate DateTime,
    @newStartDate DateTime,
    @userKey uniqueidentifier
AS
SET NOCOUNT ON

-- Check if there are any existing GroupMemberDetail records for this Group, if no records are found return with an error code of -1
IF (SELECT Count(*) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey) < 1
BEGIN
    SELECT -1
    RETURN -1
END

IF (SELECT Count(*) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate = @oldStartDate) < 1
BEGIN
    SELECT -2
    RETURN -2
END

-- Verify that @newStartDate is valid
IF    (
        (SELECT DATEADD(day,1,MAX(EffectiveDate)) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate < @oldStartDate) < @newStartDate
        OR (SELECT DATEADD(day,1,MAX(EffectiveDate)) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate < @oldStartDate) IS NULL
    )
    AND
    (
        @newStartDate < (SELECT DISTINCT ExpirationDate FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate = @oldStartDate)
        OR (SELECT DISTINCT ExpirationDate FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate = @oldStartDate) IS NULL
    )
BEGIN
BEGIN TRANSACTION
    --Previous Range
    UPDATE GroupMemberDetail SET ExpirationDate = DATEADD(day,-1,@newStartDate), UpdatedOn = GetDate(), UpdatedByUserKey = @userKey
    WHERE GroupKey = @salesTeamKey AND EffectiveDate = (SELECT MAX(EffectiveDate) FROM GroupMemberDetail WHERE GroupKey = @salesTeamKey AND EffectiveDate < @oldStartDate)
    
    --Current Range
    UPDATE GroupMemberDetail SET EffectiveDate = @newStartDate, UpdatedOn = GetDate(), UpdatedByUserKey = @userKey
    WHERE GroupKey = @salesTeamKey AND EffectiveDate =  @oldStartDate
    SELECT 0
COMMIT
END
ELSE
BEGIN
    SELECT -2
    RETURN -2
END

SET NOCOUNT OFF

GO